SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
AND InternalID IN (
    SELECT InternalID
    FROM PastHistory
    WHERE ITEMTEXT LIKE '%Example%' -- Specify specific PastHistory condition between % marks
    AND RecordStatus = 1
)
AND InternalID IN (
    SELECT InternalID
    FROM REMINDERS
    WHERE REASONTEXT LIKE '%Example%' -- Specify specific reminder between % marks
    AND RECORDSTATUS = 1
    AND REMINDERDUE >= DATEADD(MONTH, -3, CAST(GETDATE() AS DATE))
    AND REMINDERDUE <= DATEADD(MONTH, 1, CAST(GETDATE() AS DATE))
)
ORDER BY surname, firstname;

-- Active Patients with a chronic condition and a reminder for Care Plan due 3 months ago to 1 month ahead